/****** Object:  StoredProcedure [dbo].[factInventory]    Script Date: 4.5.2021 12:03:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Kristinn Thor Sigurjonsson
-- Modified Date: 2021-04-13 Use Merge to eliminate risk of duplicate rows
-- Description:	SP with option of Incremental load for fSalesPosted
-- =============================================

ALTER PROCEDURE [dbo].[factInventory] 

	@InventoryRowID bigint,
	@InventoryNewRowID bigint 
	 	
AS
BEGIN
SET NOCOUNT ON;

WITH

/*Create temporary tables for the dimensions with Company Prefix*/
tItem
AS
(SELECT
		dCOM.[CompanyPrefix]
	   ,dITM.[SK_Item]
	   ,dITM.[No]
	   ,dITM.[LastItemCost]
	FROM [DW].[dItem] dITM
	LEFT JOIN [DW].[dCompany] dCOM
		ON dCOM.[SK_Company] = dITM.[Company]),

tLocation
AS
(SELECT
		dCOM.CompanyPrefix
	   ,dLOC.SK_Location
	   ,dLOC.LocationCode
	FROM [DW].[dLocation] dLOC
	LEFT JOIN [DW].[dCompany] dCOM
		ON dCOM.[SK_Company] = dLOC.[Company]),

tItemVariant
AS
(SELECT
		dCOM.[CompanyPrefix]
	   ,dIVA.[SK_ItemVariant]
	   ,dIVA.[Item No_]
	   ,dIVA.[Code]
	FROM [DW].[dItemVariant] dIVA
	LEFT JOIN [DW].[dCompany] dCOM
		ON dCOM.[SK_Company] = dIVA.[Company]),

tVendor
AS
(SELECT
		dCOM.[CompanyPrefix]
	   ,dVEN.SK_Vendor
	   ,dVEN.VendorNo
	FROM [DW].[dVendor] dVEN
	LEFT JOIN [DW].[dCompany] dCOM
		ON dCOM.[SK_Company] = dVEN.[Company]),

tReturnReason
AS
(SELECT
		dCOM.[CompanyPrefix]
	   ,dRER.[SK_ReturnReason]
	   ,dRER.[Code]
	FROM [DW].[dReturnReason] dRER
	LEFT JOIN [DW].[dCompany] dCOM
		ON dCOM.[SK_Company] = dRER.[Company]),

/*Create temp Item ledger entry staging table with the changes from the old staging query*/
tItemLedgerEntry
AS
(SELECT
		sILE.[CompanyPrefix]
	   ,sILE.[bigint_timestamp] AS [RowID]
	   ,sILE.[Item No_]
	   ,sILE.[Entry Type]
	   ,sILE.[Entry No_]
	   ,CASE sILE.[Entry Type]
			WHEN 0 THEN sILE.[Source No_]
			ELSE NULL
		END AS [VendorNo]
	   ,sILE.[Variant Code]
	   ,sILE.[Drop Shipment]
	   ,sILE.[Location Code]
	   ,sILE.[Posting Date]
	   ,CASE
			WHEN sILE.[Return Reason Code] = '' AND
				sILE.[Entry Type] = 1 AND
				sILE.[Quantity] > 0 THEN 'NA'
			WHEN sILE.[Return Reason Code] = '' AND
				sILE.[Entry Type] = 0 AND
				sILE.[Quantity] < 0 THEN 'NA'
			ELSE NULLIF(sILE.[Return Reason Code], '')
		END AS [Return Reason Code]
	   ,SUM(sILE.[Quantity]) AS [Quantity]
	   ,SUM(sILE.[Invoiced Quantity]) AS [Invoiced Quantity]
	   ,SUM(sILE.[Remaining Quantity]) AS [Remaining Quantity]
	FROM [stg$Item Ledger Entry] sILE
	GROUP BY sILE.[CompanyPrefix]
			,sILE.[bigint_timestamp]
			,sILE.[Item No_]
			,sILE.[Entry Type]
			,sILE.[Entry No_]
			,sILE.[Quantity]
			,sILE.[Variant Code]
			,sILE.[Drop Shipment]
			,sILE.[Location Code]
			,sILE.[Posting Date]
			,sILE.[Return Reason Code]
			,sILE.[Source No_]),

/*Create temporary Item Adjusment table from staging script*/
tItemAdjustment
AS
(SELECT DISTINCT
		sVAE.[CompanyPrefix]
	   ,sVAE.[Item No_]
	   ,sVAE.[Variant Code]
	   ,sVAE.[Location Code]
	   ,sVAE.[Document No_]
	   ,sVAE.[Item Ledger Entry No_]
	   ,sVAE.[Reason Code]
	   ,sVAE.[bigint_timestamp] AS [RowID]
	FROM [stg$Value Entry] sVAE
	WHERE sVAE.[Item Ledger Entry Type] BETWEEN 2 AND 3
	AND sVAE.[Reason Code] != ''
	AND sVAE.[Adjustment] = 0),

/*Create temporary table for fItemCostaAdjustment*/
tItemCostAdjustment
AS
(SELECT
		dCOM.[CompanyPrefix]
	   ,fICA.[Item No_]
	   ,fICA.[Variant Code]
	   ,fICA.[Location Code]
	   ,fICA.[ValidFromDate]
	   ,fICA.[ValidToDate]
	   ,fICA.[WeightedItemAverageCost]
	FROM [DW].[fItemCostAdjustment] fICA
	LEFT JOIN [DW].[dCompany] dCOM
		ON dCOM.[SK_Company] = fICA.[Company]),

/*Combine all Item ledger entries into one and join with dimensions*/
tAllItemLedgerEntry
AS
(SELECT
		tILE.[CompanyPrefix]
	   ,tILE.[Posting Date]
	   ,COALESCE(tITE.[SK_Item], -1) AS [SK_Item]
	   ,COALESCE(tLOC.SK_Location, -1) AS [SK_Location]
	   ,COALESCE(tITV.[SK_ItemVariant], -1) AS [SK_ItemVariant]
	   ,COALESCE(tILE.[Return Reason Code], '-1') AS [Return Reason Code]
	   ,COALESCE(tRER.SK_ReturnReason, -1) AS [SK_ReturnReason]
	   ,COALESCE(tVEN.[SK_Vendor], -1) AS [SK_Vendor]
	   ,CASE
			WHEN SUM(tILE.[Quantity]) OVER (PARTITION BY tITE.[SK_Item], tITV.[SK_ItemVariant], tLOC.[SK_Location] ORDER BY tILE.[Posting Date]) <= 0 THEN tILE.[Posting Date]
			ELSE NULL
		END AS [OutOfStockDate]
	   ,CASE
			WHEN SUM(tILE.[Quantity]) OVER (PARTITION BY tITE.[SK_Item], tITV.[SK_ItemVariant], tLOC.[SK_Location] ORDER BY tILE.[Posting Date]) <= 0 THEN 1
			ELSE NULL
		END AS [OutOfStockBol]
	   ,tILE.Quantity AS [Quantity]
	   ,tILE.[Remaining Quantity] AS [RemainingQuantity]
	   ,tILE.[Invoiced Quantity] AS [InvoicedQuantity]
	   ,COALESCE(tICA.[WeightedItemAverageCost], tITE.[LastItemCost]) AS [ItemCost]
	   ,tILE.[Entry Type]
	   ,CASE tILE.[Entry Type]
			WHEN 0 THEN 'Purchase'
			WHEN 1 THEN 'Sale'
			WHEN 2 THEN 'Positive Adjmt'
			WHEN 3 THEN 'Negative Adjmt'
			WHEN 4 THEN 'Transfer'
			WHEN 5 THEN 'Consumption'
			WHEN 6 THEN 'Output'
		END AS [Entry Type Description]
	   ,GETUTCDATE() AS [Batchdate]
	   ,tILE.[RowID]
	FROM [tItemLedgerEntry] tILE
	LEFT JOIN [tItemCostAdjustment] tICA
		ON tICA.[CompanyPrefix] = tILE.[CompanyPrefix]
		AND tICA.[Item No_] = tILE.[Item No_]
		AND tICA.[Variant Code] = tILE.[Variant Code]
		AND tICA.[Location Code] = tILE.[Location Code]
		AND tILE.[Posting Date] BETWEEN tICA.ValidFromDate AND tICA.ValidToDate
	LEFT JOIN [tItem] tITE
		ON tILE.[CompanyPrefix] = tITE.[CompanyPrefix]
		AND tILE.[Item No_] = tITE.[No]
	LEFT JOIN [tLocation] tLOC
		ON tILE.[CompanyPrefix] = tLOC.[CompanyPrefix]
		AND tILE.[Location Code] = tLOC.LocationCode
	LEFT JOIN [tItemVariant] tITV
		ON tILE.[CompanyPrefix] = tITV.[CompanyPrefix]
		AND tILE.[Item No_] = tITV.[Item No_]
		AND tILE.[Variant Code] = tITV.[Code]
	LEFT JOIN [tVendor] tVEN
		ON tILE.[CompanyPrefix] = tVEN.[CompanyPrefix]
		AND tILE.VendorNo = tVEN.VendorNo
	LEFT JOIN [tItemAdjustment] tITA --This table is not a direct staging table
		ON tILE.[CompanyPrefix] = tITA.[CompanyPrefix]
		AND tILE.[Entry No_] = tITA.[Item Ledger Entry No_]
	LEFT JOIN [tReturnReason] tRER
		ON COALESCE(tITA.[Reason Code], tILE.[Return Reason Code]) = tRER.Code
		AND tILE.[CompanyPrefix] = tRER.CompanyPrefix  

	WHERE tILE.RowID > @InventoryRowID
	AND tILE.RowID <= @InventoryNewRowID)

,tAllItemLedgerEntryRanked as ( SELECT
		*
	   ,DENSE_RANK() OVER (PARTITION BY [CompanyPrefix], [Posting Date], [SK_Item], [SK_Location], [SK_ReturnReason], [SK_ItemVariant], [SK_Vendor], [Entry Type Description] ORDER BY [RowID] DESC) AS [Rank]
	FROM tAllItemLedgerEntry)

MERGE [DW].[fInventory] AS Target USING 
	(SELECT
		COALESCE(dCOM.[SK_Company],-1) AS [Company]
	   ,tAIL.[Posting Date] AS [PostedDate]
	   ,AVG(tAIL.[ItemCost]) AS [ItemCost]
	   ,tAIL.[SK_Item]
	   ,tAIL.[SK_Location]
	   ,tAIL.[SK_ReturnReason]
	   ,tAIL.[SK_ItemVariant]
	   ,tAIL.[SK_Vendor]
	   ,tAIL.[Entry Type]
	   ,tAIL.[Entry Type Description]
	   ,SUM(tAIL.[Quantity]) AS [SumQuantity]
	   ,SUM(tAIL.[RemainingQuantity]) AS [RemainingQuantity]
	   ,SUM(tAIL.[InvoicedQuantity]) AS [InvoicedQuantity]
	   ,tAIL.[OutOfStockDate]
	   ,IIF(LAG(tAIL.[OutOfStockDate]) OVER (PARTITION BY tAIL.[SK_Item], tAIL.[SK_Location], tAIL.[SK_ItemVariant] ORDER BY tAIL.[Posting Date]) IS NULL AND tAIL.[OutOfStockBol] = 1, 1, NULL) AS [InitialOutOfStock]
	   ,MAX(tAIL.[Batchdate]) AS [Batchdate]
	   ,MAX(tAIL.[RowID]) AS [RowID]
	FROM tAllItemLedgerEntryRanked tAIL
	LEFT JOIN [DW].[dCompany] dCOM
		ON tAIL.[CompanyPrefix] = dCOM.[CompanyPrefix]
	WHERE tAIL.[Rank] = 1
	GROUP BY dCOM.[SK_Company]
			,tAIL.[CompanyPrefix]
			,tAIL.[Posting Date]
			,tAIL.[SK_Item]
			,tAIL.[SK_Location]
			,tAIL.[SK_ItemVariant]
			,tAIL.[SK_Vendor]
			,tAIL.[Entry Type]
			,tAIL.[SK_ReturnReason]
			,tAIL.[Entry Type Description]
			,tAIL.[OutOfStockDate]
			,tAIL.[OutOfStockBol]
	) AS Source
	ON Target.[Company] = Source.[Company]
	AND Target.[PostedDate] = Source.[PostedDate] 
	AND Target.[SK_Item] = Source.[SK_Item] 
	AND Target.[SK_Location] = Source.[SK_Location] 
	AND Target.[SK_ReturnReason] = Source.[SK_ReturnReason] 
	AND Target.[SK_ItemVariant] = Source.[SK_ItemVariant] 
	AND Target.[SK_Vendor] = Source.[SK_Vendor] 
	AND Target.[Entry Type] = Source.[Entry Type]

	WHEN MATCHED
	THEN UPDATE
		SET [ItemCost] = Source.[ItemCost]
	,[Entry Type] = Source.[Entry Type]
	,[Entry Type Description] = Source.[Entry Type Description]
	,[Quantity] = Source.[SumQuantity]
	,[RemainingQuantity] = Source.[RemainingQuantity]
	,[InvoicedQuantity] = Source.[InvoicedQuantity]
	,[OutOfStockDate] = Source.[OutOfStockDate]
	,[InitialOutOfStock] = Source.[InitialOutOfStock]
	,[Batchdate] = Source.[Batchdate]
	,[RowID] = Source.[RowID]


	WHEN NOT MATCHED BY TARGET
	THEN INSERT
	([Company]
           ,[PostedDate]
           ,[ItemCost]
           ,[SK_Item]
           ,[SK_Location]
           ,[SK_ReturnReason]
           ,[SK_ItemVariant]
           ,[SK_Vendor]
           ,[Entry Type]
           ,[Entry Type Description]
           ,[Quantity]
           ,[RemainingQuantity]
           ,[InvoicedQuantity]
           ,[OutOfStockDate]
           ,[InitialOutOfStock]
           ,[Batchdate]
           ,[RowID])
     VALUES
           (Source.[Company]
           ,Source.[PostedDate]
           ,Source.[ItemCost]
           ,Source.[SK_Item]
           ,Source.[SK_Location]
           ,Source.[SK_ReturnReason]
           ,Source.[SK_ItemVariant]
           ,Source.[SK_Vendor]
           ,Source.[Entry Type]
           ,Source.[Entry Type Description]
           ,Source.[SumQuantity]
           ,Source.[RemainingQuantity]
           ,Source.[InvoicedQuantity]
           ,Source.[OutOfStockDate]
           ,Source.[InitialOutOfStock]
           ,Source.[Batchdate]
           ,Source.[RowID])
;
END

SELECT
	'RowCount' = @@rowcount